﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using KPIReport.Library;
using KPIReport.Library.DomainObject.Administration.Catalog;
using KPIReport.Library.Enum;
using KPIReport.Library.Interfaces;

namespace KPIReport.Library.DataAccess.Administration.Catalog
{
    public partial class transaksidtlRepo
    {
        public bool SaveUpload(List<transaksidtl> domains)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    domains.ForEach(delegate(transaksidtl domain)
                    {
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = @"INSERT INTO dbo.transaksidtl_temp(
                        [Customer Code]
                        ,[Salesman Code]
                        ,[Invoice Number]
                        ,[Invoice Date]
                        ,[Product Code]
                        ,[Product Description]
                        ,[Product Index]
                        ,[Selling Type]
                        ,[Product Quantity]
                        ,[UOM]
                        ,[Default UOM]
                        ,[Product List Price]
                        ,[UOM List Price]
                        ,[Gross Amount]
                        ,[Product Discount]
                        ,[Promo Discount]
                        ,[Sales Discount]
                        ,[Amount]
                        ,[Tax Amount]
                        ,[Amount After Tax]
                        ,[Customer Discount Amount]
                        ,[Case_Pallet_Tr]
                        ,[DSP_Case_Tr]
                        ,[EA_Case_Tr]
                        ,[Qty_EA]
                        ,[Qty_Karton]) 
                         VALUES(
                         @Customer_Code
                        , @Salesman_Code
                        , @Invoice_Number
                        , @Invoice_Date
                        , @Product_Code
                        , @Product_Description
                        , @Product_Index
                        , @Selling_Type
                        , @Product_Quantity
                        , @UOM
                        , @Default_UOM
                        , @Product_List_Price
                        , @UOM_List_Price
                        , @Gross_Amount
                        , @Product_Discount
                        , @Promo_Discount
                        , @Sales_Discount
                        , @Amount
                        , @Tax_Amount
                        , @Amount_After_Tax
                        , @Customer_Discount_Amount
                        , @CasePalletTr
                        , @DSPCaseTr
                        , @EACaseTr
                        , @QtyEA
                        , @QtyKarton
                        )";
                        cmd.CommandType = CommandType.Text;

                        //cmd.CommandText = "INSERT INTO dbo.transaksidtl(Customer Code, Salesman Code, Invoice Number, Invoice Date, Product Code, Product Description, Product Index, Selling Type, Product Quantity, UOM, Default UOM, Product List Price, UOM List Price, Gross Amount, Product Discount, Promo Discount, Sales Discount, Amount, Tax Amount, Amount After Tax, Customer Discount Amount, Case_Pallet_Tr, DSP_Case_Tr, EA_Case_Tr, Qty_EA, Qty_Karton) VALUES(@Customer Code, @Salesman Code, @Invoice Number, @Invoice Date, @Product Code, @Product Description, @Product Index, @Selling Type, @Product Quantity, @UOM, @Default UOM, @Product List Price, @UOM List Price, @Gross Amount, @Product Discount, @Promo Discount, @Sales Discount, @Amount, @Tax Amount, @Amount After Tax, @Customer Discount Amount, @CasePalletTr, @DSPCaseTr, @EACaseTr, @QtyEA, @QtyKarton)";
                        //cmd.CommandText = "SP_transaksidtl_Insert";
                        
                        //cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Customer_Code", SqlDbType.VarChar).Value = (object)domain.Customer_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Salesman_Code", SqlDbType.VarChar).Value = (object)domain.Salesman_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Invoice_Number", SqlDbType.VarChar).Value = (object)domain.Invoice_Number ?? DBNull.Value;
                        cmd.Parameters.Add("@Invoice_Date", SqlDbType.DateTime).Value = (object)domain.Invoice_Date ?? DBNull.Value;
                        cmd.Parameters.Add("@Product_Code", SqlDbType.VarChar).Value = (object)domain.Product_Code ?? DBNull.Value;
                        cmd.Parameters.Add("@Product_Description", SqlDbType.VarChar).Value = (object)domain.Product_Description ?? DBNull.Value;
                        cmd.Parameters.Add("@Product_Index", SqlDbType.Float).Value = (object)domain.Product_Index ?? DBNull.Value;
                        cmd.Parameters.Add("@Selling_Type", SqlDbType.VarChar).Value = (object)domain.Selling_Type ?? DBNull.Value;
                        cmd.Parameters.Add("@Product_Quantity", SqlDbType.Float).Value = (object)domain.Product_Quantity ?? DBNull.Value;
                        cmd.Parameters.Add("@UOM", SqlDbType.VarChar).Value = (object)domain.UOM ?? DBNull.Value;
                        cmd.Parameters.Add("@Default_UOM", SqlDbType.VarChar).Value = (object)domain.Default_UOM ?? DBNull.Value;
                        cmd.Parameters.Add("@Product_List_Price", SqlDbType.Float).Value = (object)domain.Product_List_Price ?? DBNull.Value;
                        cmd.Parameters.Add("@UOM_List_Price", SqlDbType.Float).Value = (object)domain.UOM_List_Price ?? DBNull.Value;
                        cmd.Parameters.Add("@Gross_Amount", SqlDbType.Float).Value = (object)domain.Gross_Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@Product_Discount", SqlDbType.Float).Value = (object)domain.Product_Discount ?? DBNull.Value;
                        cmd.Parameters.Add("@Promo_Discount", SqlDbType.Float).Value = (object)domain.Promo_Discount ?? DBNull.Value;
                        cmd.Parameters.Add("@Sales_Discount", SqlDbType.Float).Value = (object)domain.Sales_Discount ?? DBNull.Value;
                        cmd.Parameters.Add("@Amount", SqlDbType.Float).Value = (object)domain.Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@Tax_Amount", SqlDbType.Float).Value = (object)domain.Tax_Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@Amount_After_Tax", SqlDbType.Float).Value = (object)domain.Amount_After_Tax ?? DBNull.Value;
                        cmd.Parameters.Add("@Customer_Discount_Amount", SqlDbType.Float).Value = (object)domain.Customer_Discount_Amount ?? DBNull.Value;
                        cmd.Parameters.Add("@CasePalletTr", SqlDbType.Float).Value = (object)domain.CasePalletTr ?? DBNull.Value;
                        cmd.Parameters.Add("@DSPCaseTr", SqlDbType.Float).Value = (object)domain.DSPCaseTr ?? DBNull.Value;
                        cmd.Parameters.Add("@EACaseTr", SqlDbType.Float).Value = (object)domain.EACaseTr ?? DBNull.Value;
                        cmd.Parameters.Add("@QtyEA", SqlDbType.Float).Value = (object)domain.QtyEA ?? DBNull.Value;
                        cmd.Parameters.Add("@QtyKarton", SqlDbType.Float).Value = (object)domain.QtyKarton ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    });

                    SqlCommand cmdDelete = conn.CreateCommand();
                    cmdDelete.CommandText = @"DELETE transaksidtl WHERE [Invoice Number] IN (SELECT [Invoice Number] FROM transaksidtl_temp)";
                    cmdDelete.CommandType = CommandType.Text;

                    cmdDelete.Transaction = txn;
                    cmdDelete.ExecuteNonQuery();
                    cmdDelete.Dispose();

                    SqlCommand cmdInsert = conn.CreateCommand();
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("insert into transaksidtl ([Customer Code],[Salesman Code],[Invoice Date],[Product Code],[Product Description],");
                    sb.AppendLine("[Product Index],[Selling Type],[Product Quantity],UOM,[Default UOM],[Product List Price],");
                    sb.AppendLine("[UOM List Price],[Gross Amount],Amount,[Tax Amount],[Amount After Tax],[invoice number])");
                    sb.AppendLine("select [Customer Code],[Salesman Code],[Invoice Date],[Product Code],[Product Description],");
                    sb.AppendLine("[Product Index],[Selling Type],[Product Quantity],UOM,[Default UOM],[Product List Price],");
                    sb.AppendLine("[UOM List Price],[Gross Amount],Amount,[Tax Amount],[Amount After Tax],[invoice number] from transaksidtl_temp");
                    sb.AppendLine("where YEAR([invoice date]) in (select distinct YEAR([invoice date]) from transaksidtl_temp) and MONTH([Invoice Date]) in(select distinct MONTH([Invoice Date]) from transaksidtl_temp)");

                    cmdInsert.CommandText = sb.ToString();
                    cmdInsert.CommandType = CommandType.Text;
                    cmdInsert.Transaction = txn;
                    cmdInsert.ExecuteNonQuery();
                    cmdInsert.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }


            }
            return result;
        }
    }
}
